package com.project.dao.impl;

import com.project.bean.CutPageBean;
import com.project.bean.RoomBean;
import com.project.dao.IRoomDao;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository
public class RoomDaoImpl extends BaseDao implements IRoomDao {
    @Override
    public CutPageBean<RoomBean> findByItem(int pageNO, String address, String state, String info, String type) {
        CutPageBean<RoomBean> roomCut = new CutPageBean<>();
        String sql = " where 1=1  ";
        List value = new ArrayList<>();
        if (address != null && address.length() != 0) {
            sql += "and  r_address like ? ";
            value.add("%" + address + "%");
        }
        if (state != null && state.length() != 0) {
            sql += "and r_state= ? ";
            value.add(state);
        }
        String endSql = " having 1=1 ";
        String endSql2 = "where 1=1 ";
        if (info != null && info.length() != 0) {
            if ("不限".equals(info)||"".equals(info)) {
                endSql += "and r_max>=num ";
                endSql2 += "and num<=m.`r_max` ";
            }
            if ("可住".equals(info)) {
                endSql += "and r_max>num ";
                endSql2 += "and  num<m.`r_max`";
            }
        }
        if (type != null && type.length() != 0) {
            sql += "and r_type= ? ";
            value.add(type);
        }

        this.setConnection();
        try {
            ps = con.prepareStatement("SELECT r.* ,COUNT(s.`pk_studentId`) num FROM t_room  r LEFT JOIN  t_student s  ON  r.`pk_roomId`=s.`fk_roomId`  " + sql + "  GROUP BY r.`pk_roomId` " + endSql + "LIMIT ?,?;");
            for (int i = 0; i < value.size(); i++) {
                ps.setObject(i + 1, value.get(i));
            }
            ps.setObject(value.size() + 1, (pageNO - 1) * CutPageBean.PAGESIZI);
            ps.setObject(value.size() + 2, CutPageBean.PAGESIZI);
            rs = ps.executeQuery();
            while (rs.next()) {
                RoomBean roomBean = new RoomBean();
                roomBean.setId(rs.getInt("pk_roomId"));
                roomBean.setAddress(rs.getString("r_address"));
                roomBean.setMaxNumber(rs.getInt("r_max"));
                roomBean.setState(rs.getString("r_state"));
                roomBean.setType(rs.getString("r_type"));
                roomBean.setNumber(rs.getInt("num"));
                roomCut.getList().add(roomBean);
            }

            ps = con.prepareStatement("SELECT COUNT(DISTINCT m.`pk_roomId`) s FROM t_room m  ,(SELECT COUNT(t.`pk_studentId`) num ,r.`pk_roomId` c,r.* FROM t_room r LEFT JOIN t_student t\n" +
                    " ON r.`pk_roomId`=t.`fk_roomId`" + sql + "  GROUP BY r.`pk_roomId`) c " + endSql2 + " AND c=m.`pk_roomId`");
            for (int i = 0; i < value.size(); i++) {
                ps.setObject(i + 1, value.get(i));
            }
            rs = ps.executeQuery();
            if (rs.next()) {
                roomCut.setCount(rs.getInt("s"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close();
        }
        return roomCut;
    }

    @Override
    public void add(RoomBean room) {
        this.setConnection();
        try {
            ps = con.prepareStatement("INSERT INTO t_room(r_address,r_max,r_state,r_type) VALUES(?,?,?,?)");
            ps.setObject(1, room.getAddress());
            ps.setObject(2, room.getMaxNumber());
            ps.setObject(3, room.getState());
            ps.setObject(4, room.getType());

            ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }

    }

    @Override
    public void del(Integer roomId) {
        this.setConnection();
        try {
            ps = con.prepareStatement("delete from t_room where pk_roomId = ? ");
            ps.setObject(1, roomId);

            ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }
    }

    @Override
    public RoomBean findByRoomId(Integer id) {
        this.setConnection();
        RoomBean roomBean = null;
        try {
            ps = con.prepareStatement("SELECT t.*,COUNT(s.`pk_studentId`) num FROM t_room t LEFT JOIN t_student s ON t.`pk_roomId`=s.`fk_roomId` where t.pk_roomId= ?  GROUP BY t.`pk_roomId`");
            ps.setObject(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                roomBean = new RoomBean();
                roomBean.setNumber(rs.getInt("num"));
                roomBean.setType(rs.getString("r_type"));
                roomBean.setId(rs.getInt("pk_roomId"));
                roomBean.setMaxNumber(rs.getInt("r_max"));
                roomBean.setAddress(rs.getString("r_address"));
                roomBean.setState(rs.getString("r_state"));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            close();
        }
        return roomBean;
    }

    @Override
    public List<RoomBean> findByGender(String gender) {
        this.setConnection();
        List<RoomBean> list = new ArrayList<>();
        try {
            ps = con.prepareStatement("SELECT * FROM  (SELECT r.* ,COUNT(s.`pk_studentId`) num FROM t_room r LEFT JOIN t_student s ON r.`pk_roomId`=s.`fk_roomId` where r.r_type like ?  GROUP BY r.`pk_roomId` HAVING r.`r_max`>num)c;");
            ps.setObject(1, "%" + gender + "%");
            rs = ps.executeQuery();
            while (rs.next()) {
                RoomBean roomBean = new RoomBean();
                roomBean.setId(rs.getInt("pk_roomId"));
                roomBean.setAddress(rs.getString("r_address"));
                roomBean.setMaxNumber(rs.getInt("r_max"));
                roomBean.setState(rs.getString("r_state"));
                roomBean.setType(rs.getString("r_type"));
                roomBean.setNumber(rs.getInt("num"));
                list.add(roomBean);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }

        return list;
    }

    @Override
    public void updateRoomStatus(int roomId, String roomStatus) {
        this.setConnection();
        try {
            ps = con.prepareStatement("update t_room set r_state = ? where pk_roomId = ? ");
            ps.setObject(1, roomStatus);
            ps.setObject(2, roomId);

            ps.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            this.close();
        }

    }
}
